﻿using Kanban.Dapper.Dal;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Linq.Expressions;
using System.Text;
using System.Threading.Tasks;

namespace Kanban.Dal
{
    public abstract class BaseDal<T> where T : class, new()
    {
        public string TableName { get; set; }

        public IDbClient _db = Db.SqlDb;
        public IDbClient _WCSDB = Db.WCSDB;
        public async Task<bool> DeleteAsync(int id)
        {
            return await DeleteAsync("Id = @Id", new { id });
        }

        public async Task<bool> DeleteAsync(string ids)
        {
            string sql = $"DELETE {TableName} WHERE Id IN ({ids})";
            return await _db.ExecuteAsync(sql) > 0;
        }

        public async Task<bool> DeleteAsync(string where, object param = null)
        {
            string sql = $"DELETE {TableName} WHERE {where}";
            return await _db.ExecuteAsync(sql, param) > 0;
        }

        public Task<T> GetModelAsync(int id)
        {
            string sql = $"SELECT * FROM {TableName} WHERE Id = @Id";
            return _db.GetAsync<T>(sql, new { id });
        }

        public Task<T> GetModelAsync(string where, object param = null)
        {
            string sql = $"SELECT * FROM {TableName} WHERE {where}";
            return _db.GetAsync<T>(sql, param);
        }

        public Task<IEnumerable<T>> GetListAsync(string where, object param = null)
        {
            string sql = $"SELECT * FROM {TableName} WHERE {where}";
            return _db.ListAsync<T>(sql, param);
        }

        public async Task<Tuple<IList<object>, int>> GetModelsByPageAsync(string column, string where, string orderby, int pageSize, int pageIndex)
        {
            string sql = $@"SELECT COUNT(1) FROM {TableName} WHERE {where};
                            SELECT TOP {pageSize} *
                                FROM(SELECT ROW_NUMBER() OVER(ORDER BY {orderby}) AS RowNum,{column}
                                          FROM  {TableName} with(NOLOCK)
                                          WHERE {where}
                                        ) AS result
                                WHERE result.RowNum > {(pageIndex - 1) * pageSize}
                                ORDER BY {orderby}";
            GridReaderWapper reader = await _db.QueryMultipleAsync(sql);
            int total = await reader.ReadFirstAsync<int>();
            IEnumerable<object> data = await reader.ReadAsync<object>();
            return new Tuple<IList<object>, int>(data.ToList(), total);
        }

        public async Task<Tuple<IList<object>, int>> GetPageAsync(string table, string column, string where, string orderby, int pageSize, int pageIndex)
        {
            string sql = $@"SELECT COUNT(1) FROM {table} WHERE {where};
                            SELECT TOP {pageSize} *
                                FROM(SELECT ROW_NUMBER() OVER(ORDER BY {orderby}) AS RowNum,{column}
                                          FROM  {table}
                                          WHERE {where}
                                        ) AS result
                                WHERE result.RowNum > {(pageIndex - 1) * pageSize}
                                ORDER BY {orderby}";
            GridReaderWapper reader = await _db.QueryMultipleAsync(sql);
            int total = await reader.ReadFirstAsync<int>();
            IEnumerable<object> data = await reader.ReadAsync<object>();
            return new Tuple<IList<object>, int>(data.ToList(), total);
        }

        public Task<int> AddAsync(T model)
        {
            string paras = _db.GetParas<T>().Replace("Group", "[Group]");
            string sqls = $"insert into {TableName}({paras}) values({_db.GetATParas<T>()})";            
            return _db.ExecuteAsync(sqls, model);
        }

        public Task<int> UpdateAsync(T model)
        {
            string paras = _db.GetParasToAT<T>().Replace("Group=", "[Group]=");
            string sql = $"update {TableName} set {paras} where Id=@Id";
            return _db.ExecuteAsync(sql, model);
        }
    }
}
